import simplejson, sqlite3, csv
from geohash import *

def geojson2sqlite(sqlite_path, geojson_path, layer):
    #load the json
    f = open(geojson_path)
    content =  f.read()
    print 'length:', len(content)
    print content[:100]
    geojson = simplejson.loads(str(content))
    f.close()
    features = geojson['features']
    h = features[0]['properties'].keys()
    h.append('geohash')
    h.append('geojson')
    #import into sqlite
    conn = sqlite3.connect(sqlite_path)
    c = conn.cursor()
    c.execute('PRAGMA synchronous=OFF')
    c.execute('PRAGMA temp_store = MEMORY')
    #create the table
    sql = 'create table %s (%s)' % (layer, ','.join(['%s text' % x for x in h]))
    c.execute(sql)
    for feature in features:
        
        p = feature['properties']
        l = p.values()
        x = float(p['x'])
        y = float(p['y'])
        gh = str(Geohash((x, y)))
        geojson = simplejson.dumps(feature)
        l.append(gh)
        l.append(geojson)
        sql = 'insert into %s values (%s)' % (layer, ','.join(['?']*len(h)))
        c.execute(sql, l)
    conn.commit()

def geojson2sqlite_feature_table(sqlite_path, geojson_path, layer):
    #load the json
    f = open(geojson_path)
    content = f.read()
    f.close()
    print content[60:200]
    geojson = simplejson.loads(str(content))
    
    features = geojson['features']
    h = features[0]['properties'].keys()
    h.append('geohash')
    h.append('geojson')
    #import into sqlite
    conn = sqlite3.connect(sqlite_path)
    c = conn.cursor()
    c.execute('PRAGMA synchronous=OFF')
    c.execute('PRAGMA temp_store = MEMORY')
    #create the table
    sql = 'create table if not exists feature (layer text, geohash text, geojson text)'
    c.execute(sql)
    sql = 'insert into feature values (?, ?, ?)'
    for feature in features:
        
        p = feature['properties']
        l = p.values()
        x = float(p['x'])
        y = float(p['y'])
        gh = str(Geohash((x, y)))
        geojson = feature
        l.append(gh)
        l.append(geojson)
        if feature['geometry']['type']=='LineString':
            
            #the point
            geojson['geometry'] =  {"type": "Point", "coordinates": [x, y]}
            c.execute(sql, [layer, gh, simplejson.dumps(geojson)])
            #polyline add two features, a point and a line
            #the line
            
            geojson['geometry'] = feature['geometry']
            #remove the properties, it is only for graphical purposes.
            #feature['properties'] = {}
            c.execute(sql, [layer, gh, simplejson.dumps(geojson)])
        else:
           
            c.execute(sql, [layer, gh, simplejson.dumps(geojson)])
    conn.commit()




def csv2sqlite(csv_path, sqlite_path, layer_name):
    f = open(csv_path)
    reader = csv.reader(f)
    h = reader.next()
    h.append('geohash')
    h.append('geojson')
    conn = sqlite3.connect(sqlite_path)
    c = conn.cursor()
    c.execute('PRAGMA synchronous=OFF')
    c.execute('PRAGMA temp_store = MEMORY')
    
    sql = 'create table %s (%s)' % (layer_name, ','.join(['%s text' % x for x in h]))
    c.execute(sql)
    for l in reader:
        sql = 'insert into %s values (%s)' % (layer_name, ','.join(['?']*len(h)))
        r = dict(zip(h[:-1], l))
        lat = float(r['lat'])
        lon = float(r['lon'])
        gh = str(Geohash((lon, lat)))
        l.append(gh)
        geojson = {'type': 'Feature', 'geometry': {"type": "Point", "coordinates": [lon, lat]}, 'properties': dict(zip(h[:-1], l[:-1]))}
        l.append(simplejson.dumps(geojson))
        c.execute(sql, l)
    conn.commit()

def csv2sqlite_feature_layer(csv_path, sqlite_path, layer_name):
    f = open(csv_path)
    reader = csv.reader(f)
    h = reader.next()
    h.append('geohash')
    h.append('geojson')
    conn = sqlite3.connect(sqlite_path)
    c = conn.cursor()
    c.execute('PRAGMA synchronous=OFF')
    c.execute('PRAGMA temp_store = MEMORY')
    
    sql = 'create table if not exists feature (layer text, geohash text, geojson text)'
    c.execute(sql)
    for l in reader:
        
        r = dict(zip(h[:-1], l))
        lat = float(r['lat'])
        lon = float(r['lon'])
        gh = str(Geohash((lon, lat)))
        l.append(gh)
        geojson = {'type': 'Feature', 'geometry': {"type": "Point", "coordinates": [lon, lat]}, 'properties': dict(zip(h[:-1], l[:-1]))}
        sql = 'insert into feature values (?, ?, ?)'
        c.execute(sql, [layer_name, gh, simplejson.dumps(geojson)])

    conn.commit()

#    
#csv_path = 'storm_nodes.csv'
#sqlite_path = 'vector.sqlite'
#layer_name = 'storm_nodes'
#geojson2sqlite(sqlite_path, geojson_path, layer)
#geojson2sqlite_feature_table(sqlite_path, geojson_path, layer)
#
#
#csv_path = 'storm_nodes.csv'
#sqlite_path = 'vector.sqlite'
#layer_name = 'storm_nodes'
#csv2sqlite(csv_path, sqlite_path, layer_name)
#csv2sqlite_feature_layer(csv_path, sqlite_path, layer_name)


geojson_path = 'm.geojson'
sqlite_path = '../data/vector.sqlite'
layer_name = 'sanitary_nodes'

geojson2sqlite_feature_table(sqlite_path, geojson_path, layer_name)

print layer_name, ' is imported'

geojson_path = 'n.geojson'
sqlite_path = '../data/vector.sqlite'
layer_name = 'sanitary_mains'
#geojson2sqlite(sqlite_path, geojson_path, layer)
geojson2sqlite_feature_table(sqlite_path, geojson_path, layer_name)

print layer_name, ' is imported'